package com.yingda.utils;

import android.content.Context;
import android.os.Environment;
import android.os.Handler;
import android.os.Looper;
import android.text.TextUtils;
import android.util.Log;

import com.hjq.toast.ToastUtils;
import com.yingda.ad.Constants;
import com.yingda.bean.Student;
import com.yingda.bean.WorkBean;
import com.yingda.eventbus.EventBus;


import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
    private static ExcelUtils instance;
    public static final String TAG = "ExcelUtils";
    private Handler handler = new Handler(Looper.getMainLooper());

    private ExcelUtils () {}

    public static synchronized ExcelUtils getInstance() {
        if (instance == null) {
            instance = new ExcelUtils();
        }
        return instance;
    }


    public void createExcel(List<WorkBean> workBeans, Context context){
        //初始化HSSFWorkbook
        HSSFWorkbook mWorkbook = new HSSFWorkbook();
        //创建HSSFSheet
        HSSFSheet mSheet = mWorkbook.createSheet("mysheet");
        //创建创建Excel标题行（第一行）
        createExcelHead(mSheet);
//        for (Student student : students) {
//            Log.d(TAG, "onCreate: " + student.id + "," + student.name + "," + student.gender + "," + student.age);
//            //向Excel写入的一行数据
//            createCell(student.id, student.name, student.gender, student.age, mSheet);
//        }

        for (WorkBean workBean : workBeans) {
            Log.d(TAG, "onCreate: " + workBean.getId() + "," + workBean.getName());
            //向Excel写入的一行数据
            createCell(workBean.getId(), workBean.getName(), mSheet);
        }

        SimpleDateFormat df = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");//设置日期格式
        //在sdcard自定义路径(app数据不会随app的删除而删除)
        //String fileDir = "/sdcard/Mips/" + df.format(new Date()) + ".xls";
        //File xlsFile = new File(fileDir);
        //获取路径(app数据会随app的删除而删除)
//        File fileDir = Environment.getExternalStorageDirectory();
//        //File file = new File(sdcard, "filename.txt");
//        String path = fileDir.getAbsoluteFile() + File.separator + "Download";
//        // File fileDir = App.getInstance().getExternalCacheDir();
//        File xlsFile = new File(path,df.format(new Date()) + ".xls");

//        File fileDir = Environment.getExternalStorageDirectory();
//        String path = fileDir.getAbsoluteFile() + File.separator +"MyFile";

        File path;
        if (android.os.Build.VERSION.SDK_INT >= android.os.Build.VERSION_CODES.Q) {
            // 设备运行的是 Android 10 或更高版本
            path = new File(context.getExternalFilesDir(null), "MyFile");
        } else {
            // 设备运行的是 Android 9 或更低版本
            File fileDir = Environment.getExternalStorageDirectory();
            path = new File(fileDir,"MyFile");
        }

        File xlsFile = new File(path,"表格3.xlsx");

        boolean x = FileUtils.createOrExistsFile(path);
        Log.d(TAG, "[fileDir]"+path);
        Log.d(TAG, "onCreate[x]: " + x);
        try {
            if (!xlsFile.exists()) {
                xlsFile.createNewFile();
                Log.d(TAG, "创建文件");
            }
            handler.post(new Runnable() {
                @Override
                public void run() {
                    ToastUtils.show("创建文件成功");
                }
            });
            Log.d(TAG, "创建文件成功");
            SharedPreferenceUtil.getInstance(context).setString("excelPath",xlsFile.getAbsolutePath());
            //将HSSFWorkbook写入文件
            mWorkbook.write(new FileOutputStream(xlsFile));// 或者以流的形式写入文件 mWorkbook.write(new FileOutputStream(xlsFile));
            mWorkbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //导出到电脑上
            //adb push /sdcard/Download/2024_10_25_16_39_57.xls ./
            EventBus.getDefault().post("关闭dialog", Constants.DIALOGCLOSE);
        }

    }


    // 创建Excel标题行，第一行。
    private void createExcelHead(HSSFSheet mSheet) {
        HSSFRow headRow = mSheet.createRow(0);
        headRow.createCell(0).setCellValue("id");
        headRow.createCell(1).setCellValue("name");
//        headRow.createCell(2).setCellValue("gender");
//        headRow.createCell(3).setCellValue("age");
    }

    // 创建Excel的一行数据。
    private static void createCell(String id, String name, HSSFSheet sheet) {
        HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
        dataRow.createCell(0).setCellValue(id);
        dataRow.createCell(1).setCellValue(name);

    }


    public  List<List<Object>> readExcel(InputStream inputStream,File fileName) {
        List<List<Object>> rowDataList = new ArrayList<>();
        try {
            Workbook workbook ;
            // 判断文件格式
            if (fileName.getAbsolutePath().endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);  // 处理 .xls 格式
            } else if (fileName.getAbsolutePath().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);  // 处理 .xlsx 格式
            } else {
                Log.e("ExcelReader", "Unsupported file format");
                return null;
            }

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 遍历每一行
            for (Row row : sheet) {
                List<Object> rowData = new ArrayList<>();

                // 遍历每一行中的每一个单元格
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    switch (cell.getCellType()) {
                        case STRING:
                            rowData.add(cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            rowData.add(String.valueOf(cell.getNumericCellValue()));
                            break;
                        case BOOLEAN:
                            rowData.add(String.valueOf(cell.getBooleanCellValue()));
                            break;
                        case FORMULA:
                            rowData.add(cell.getCellFormula());
                            break;
                        case BLANK:
                            rowData.add("");  // 空单元格处理
                            break;
                        default:
                            rowData.add("Unknown Type");
                            break;
                    }
                }
                rowDataList.add(rowData);  // 将每行数据加入到 rowDataList 中
            }

            // 关闭 workbook
            workbook.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return rowDataList;  // 返回所有行的数据
    }


    //开始读取表格1
    private List<WorkBean>  workBeanListFist = new ArrayList<>();
    private HashMap<String, WorkBean> workBeanListFist1 = new HashMap<>();
    public void  readTab1Excel(InputStream inputStream ,File excelPath){
        // String excelPath1 = SharedPreferenceUtil.getInstance(this).getString("excelPath");
        List<List<Object>> lists = ExcelUtils.getInstance().readExcel(inputStream,excelPath);
        Log.i(TAG, "readExcel: " + lists);
        workBeanListFist.clear();
        workBeanListFist1.clear();
        for (List<Object> list : lists) {
            if (list!=null && list.size()>0){
                for (Object o : list) {
                    //打印每一行的信息
                    if (o!=null){
                        List<WorkBean> workBeans = RegexUtils.getInstance().regexWord(o.toString());
                        if (workBeans!=null && workBeans.size()>0){
                            workBeanListFist.addAll(workBeans);
                        }
                    }

                }
            }
        }
        Log.i(TAG, "readExcel: " + workBeanListFist);
        if (workBeanListFist.size()>0){
            for (WorkBean workBean : workBeanListFist) {
                String name = workBean.getName();
                String id = workBean.getId();
                workBeanListFist1.put(id,workBean);
            }
            handler.post(() -> {
                EventBus.getDefault().post("关闭dialog", Constants.DIALOGCLOSE);
            });
        }else {
            handler.post(() -> EventBus.getDefault().post("关闭dialog", Constants.DIALOGCLOSE));
        }

        //传递数据
        EventBus.getDefault().post(workBeanListFist1,Constants.READONE);
    }


    //开始读取表格2
    private Map<String,WorkBean> workBeanList = new HashMap<>();
    public void   readTab2Excel(InputStream inputStream ,File excelPath){
        // String excelPath1 = SharedPreferenceUtil.getInstance(this).getString("excelPath");
        List<List<Object>> lists = readExcel(inputStream,excelPath);
        Log.i(TAG, "readExcel2: " + lists);
        workBeanList.clear();
        for (List<Object> list : lists) {
            if (list!=null && list.size()>0){
                for (int i = 0; i < list.size(); i++) {
                    String id = (String) list.get(1);
                    String name= (String) list.get(2);
                    WorkBean workBean = new WorkBean();
                    workBean.setId(id);
                    workBean.setName(name);
                    if (!TextUtils.isEmpty(id) && isInteger(id)){
                        workBeanList.put(id,workBean);
                    }

                }
            }
        }

        Log.i(TAG, "readExcel: " + workBeanList);
        if (workBeanList.size()>0){
            for (String key : workBeanList.keySet()) {
                WorkBean workBean = workBeanList.get(key);
                String name = workBean.getName();
                String id = workBean.getId();
              //  sb.append("name=" + name + "...id=" + id + "\n");
                //System.out.println("Key = " + key + ", Value = " + hm.get(key));
            }
            handler.post(() -> {
                EventBus.getDefault().post("关闭dialog", Constants.DIALOGCLOSE);

            });
        }else {
            EventBus.getDefault().post("关闭dialog", Constants.DIALOGCLOSE);

        }

        //传递数据
        EventBus.getDefault().post(workBeanList,Constants.READTWO);
    }

    public static boolean isInteger(String str) {
        if (str == null || str.isEmpty()) {
            return false; // 空字符串和 null 不是有效的 int
        }
        try {
            Integer.parseInt(str);
            return true;  // 成功解析为 int
        } catch (NumberFormatException e) {
            return false; // 解析失败，说明不是 int 类型
        }
    }

}

